<?php

/**
 * Capital Direct Lending (http://www.philbsd.com/cdl)
 *
 * Copyright (C) 2008 Philippine Business Solution Developers <http://www.philbsd.com>
 * All rights reserved.
 *
 *
 * LICENSE
 *
 * This source file is subject to the GNU General Public License v3 license
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-3.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@philbsd.com so we can send you a copy immediately.
 *
 * @copyright  (c) 2008 Philippine Business Solution Developers. (http://www.philbsd.com)
 * @license    http://www.gnu.org/licenses/gpl-3.0.txt
 * @version    $Id: reports.php 261 2008-05-11 16:44:27Z jundie2000 $
 */

if (!defined('BASEPATH')) exit('No direct script access allowed');

class Reports extends Model
{

	public function getAll()
	{
		$ret = array();
		$ret['collection'] = $this->getCollection();
		$ret['pending_approval'] = $this->getPendingApproval();
		$ret['pending_release'] = $this->getPendingRelease();
		return $ret;
	}

	public function getCollection($date)
	{
      
        $ret = array();
        if (empty($date)) {
            $date = date('Y-m-d');
        }
        $this->db->select('*')
                    ->from('payments')
                    ->where('due_date', $date)
                    ->order_by("loan_id", "asc");
        if ($paymentQuery = $this->db->get()) {
            foreach ($paymentQuery->result_array() as $row) {
                $this->db->select(' loan.collector_id, loan.borrower_id,
                                    bo.profile_id,
                                    prof.first_name, prof.last_name, prof.address, prof.barangay, prof.city, prof.island
                                  ')
                        ->from('loans loan')
                        ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                        ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
                        ->where('loan.id', $row['loan_id']);
                if ($loanQuery = $this->db->get()) {
                    $loan = $loanQuery->row_array();
                    $row['info'] = $loan;
                }
                $ret[] = $row;
            }
        }

		return $ret;
	}

	public function exportCollection()
	{

		$ret = array();
		$date = date('Y-m-d');
		// get all entries from payments table by due date
		$this->db->select('*')->from('payments')->where('due_date', $date);
		if ($collectionQuery = $this->db->get()) {
			foreach ($collectionQuery->result_array() as $row) {
				// get loan information
				$this->db->select('*')->from('loans')
						 ->where('id', $row['loan_id']);
				if ($loanQuery = $this->db->get()) {
					$loan = $loanQuery->row_array();
					// get borrower information
					$this->db->select('*')->from('borrowers')
							 ->where('id', $loan['borrower_id']);
					if ($borrowerQuery = $this->db->get()) {
						$borrower = $borrowerQuery->row_array();
						// get profile information
						$this->db->select('*')->from('profiles')
							 ->where('id', $borrower['profile_id']);
						if ($profileQuery = $this->db->get()) {
							$profile = $profileQuery->row_array();
							$borrower['Profile'] = $profile;
						}
						$row['Borrower'] = $borrower;
					}
					// get co-borrower information
					$this->db->select('*')->from('borrowers')
							 ->where('id', $loan['coborrower_id']);
					if ($coBorrowerQuery = $this->db->get()) {
						$coBorrower = $coBorrowerQuery->row_array();
						// get profile information
						$this->db->select('*')->from('profiles')
							 ->where('id', $coBorrower['profile_id']);
						if ($profileQuery = $this->db->get()) {
							$profile = $profileQuery->row_array();
							$coBorrower['Profile'] = $profile;
						}
						$row['CoBorrower'] = $coBorrower;
					}
				}
				$ret[] = $row;
			}
		}
		return $ret;

	}

	public function getPendingApproval()
	{
		$ret = array();
		// get all entries from loans table where status is pending approval
		$this->db->select(' loan.id,
							loan.borrower_id,
							loan.loan_amount,
							loan.application_date,
							bo.profile_id,  
							prof.last_name,
							prof.first_name,
							prof.address,
							prof.barangay,
							prof.city,
							prof.island')
                 ->from('loans loan')
                 ->join('borrowers bo', 'bo.id = loan.borrower_id', 'left')
                 ->join('profiles prof', 'prof.id = bo.profile_id', 'left')
				 ->where('status', 2);
		if ($loanQuery = $this->db->get()) {
			foreach ($loanQuery->result_array() as $row) {
				$ret[] = $row;
			}
		}
		return $ret;
	}

	public function getPendingRelease()
	{
		$ret = array();
		// get all entries from loans table where status is pending release
		$this->db->select('*')->from('loans')->where('status', 3);
		if ($loanQuery = $this->db->get()) {
			foreach ($loanQuery->result_array() as $row) {
				// get borrower information
				$this->db->select('*')->from('borrowers')
						 ->where('id', $row['borrower_id']);
				if ($borrowerQuery = $this->db->get()) {
					$borrower = $borrowerQuery->row_array();
					// get profile information
					$this->db->select('*')->from('profiles')
						 ->where('id', $borrower['profile_id']);
					if ($profileQuery = $this->db->get()) {
						$profile = $profileQuery->row_array();
						$borrower['Profile'] = $profile;
					}
					$row['Borrower'] = $borrower;
				}
				// get co-borrower information
				$this->db->select('*')->from('borrowers')
						 ->where('id', $row['coborrower_id']);
				if ($coBorrowerQuery = $this->db->get()) {
					$coBorrower = $coBorrowerQuery->row_array();
					// get profile information
					$this->db->select('*')->from('profiles')
						 ->where('id', $coBorrower['profile_id']);
					if ($profileQuery = $this->db->get()) {
						$profile = $profileQuery->row_array();
						$coBorrower['Profile'] = $profile;
					}
					$row['CoBorrower'] = $coBorrower;
				}
				$ret[] = $row;
			}
		}
		return $ret;
	}

	public function getLoan($id)
	{
		$ret = array();
		// get all entries from loans table (it can be limit down to number of records to retrieve)
		$this->db->select('*')->from('loans')
				 ->where('id', $id);
		if ($loanQuery = $this->db->get()) {
			$loan = $loanQuery->row_array();
			$this->db->select('*')->from('borrowers')
					 ->where('id', $loan['borrower_id']);
			if ($borrowerQuery = $this->db->get()) {
				$borrower = $borrowerQuery->row_array();
				// get profile information
				$this->db->select('*')->from('profiles')
					 ->where('id', $borrower['profile_id']);
				if ($profileQuery = $this->db->get()) {
					$profile = $profileQuery->row_array();
					$borrower['Profile'] = $profile;
				}
				$loan['Borrower'] = $borrower;
			}
			// get co-borrower information
			$this->db->select('*')->from('borrowers')
					 ->where('id', $loan['coborrower_id']);
			if ($coBorrowerQuery = $this->db->get()) {
				$coBorrower = $coBorrowerQuery->row_array();
				// get profile information
				$this->db->select('*')->from('profiles')
					 ->where('id', $coBorrower['profile_id']);
				if ($profileQuery = $this->db->get()) {
					$profile = $profileQuery->row_array();
					$coBorrower['Profile'] = $profile;
				}
				$loan['CoBorrower'] = $coBorrower;
			}

            $loan['total_paid'] = 0;
            // get payments information
			$this->db->select('*')->from('payments')
					 ->where('loan_id', $id);
            if ($paymentQuery = $this->db->get()) {
                $ctr = 0;
                foreach ($paymentQuery->result_array() as $row) {
                    $loan['Payment'][] = $row;
                    $loan['total_paid'] += $loan['Payment'][$ctr]['amount_paid'];
                    $ctr++;
                }
            }

			$ret = $loan;
		}
		return $ret;
	}

	public function getCustomReport($data)
	{
		$ret = array();

		$fields = array();
		foreach ($data['fields'] as $field) {
			$x = explode('.', $field);
			$f_field = $x[1];
			$x[2] = $x[0];
			if ($x[0] == 'borrowers') {
				$x[0] = 'profiles';
			} else if ($x[0] == 'coborrowers') {
				$x[0] = 'coborrower_profiles';
			} elseif ($x[0] == 'businesss') {
				$x[0] = 'businesses';
			}
			$fields[] = "{$x[0]}.{$x[1]} AS {$x[2]}_{$x[1]}";
		}

		$this->db->select(implode(', ', $fields))
				 ->from('loans')
				 ->join('borrowers', 'borrowers.id = loans.borrower_id', 'left')
				 ->join('profiles', 'profiles.id = borrowers.profile_id', 'left')
				 ->join('borrowers AS coborrowers', 'coborrowers.id = loans.coborrower_id', 'left')
				 ->join('profiles AS coborrower_profiles', 'coborrower_profiles.id = coborrowers.profile_id', 'left')
				 ->join('businesses', 'businesses.borrower_id = borrowers.id', 'right')
				 ->join('collaterals', 'collaterals.borrower_id = borrowers.id', 'right')
				 ->where("(loans.pn_signed_date >= '{$data['date_from']}' AND
						loans.pn_signed_date <= '{$data['date_to']}') OR
						(loans.start_date >= '{$data['date_from']}' AND
						loans.start_date <= '{$data['date_to']}') OR
						(loans.default_date >= '{$data['date_from']}' AND
						loans.default_date <= '{$data['date_to']}') OR
						(loans.delinquent_date >= '{$data['date_from']}' AND
						loans.delinquent_date <= '{$data['date_to']}') OR
						(loans.inrepayment_date >= '{$data['date_from']}' AND
						loans.inrepayment_date <= '{$data['date_to']}') OR
						(loans.release_pending_date >= '{$data['date_from']}' AND
						loans.release_pending_date <= '{$data['date_to']}') OR
						(loans.inquiry_date >= '{$data['date_from']}' AND
						loans.inquiry_date <= '{$data['date_to']}') OR
						(loans.application_date >= '{$data['date_from']}' AND
						loans.application_date <= '{$data['date_to']}') OR
						(loans.credit_investigation_date  >= '{$data['date_from']}' AND
						loans.credit_investigation_date  <= '{$data['date_to']}')");
		if ($customQuery = $this->db->get()) {
			foreach ($customQuery->result_array() as $row) {
				$ret[] = $row;
			}
		}

		return $ret;
	}

}